package work.utils;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.h819.core.file.excel.jxl.ExcelUtils;
import com.h819.core.utils.MyDateUtils;
import com.h819.core.utils.Utils;

/**
 * @ClassName: TiLuUtils
 * @Description: TODO(题录工具类)
 * @author h819
 * @date June 10, 2009 16:15:24 PM
 * @version V0.1
 */
public class TiLuUtils {

	private static Logger log = Logger.getLogger(TiLuUtils.class);

	// 题录表头信息
	private static ArrayList<String> title = null;

	/**
	 * 
	 */
	private void init() {

		//初始化表头信息
		title = new ArrayList<String>();
		title.set(0, "标准号");
		title.set(1, "标准中文名称");
		title.set(2, "标准英文名称");
		title.set(3, "发布日期");
		title.set(4, "实施日期");
		title.set(5, "发布部门");
		title.set(6, "提出单位");
		title.set(7, "主要起草单位");
		title.set(8, "主要起草人");
		title.set(9, "CCS");
		title.set(10, "ICS");
		title.set(11, "采标程度");
		title.set(12, "采用标准号");
		title.set(13, "采标中文名称");
		title.set(14, "主题词");
		title.set(15, "范围");
		title.set(16, "规范性引用文件");
		title.set(17, "代替标准");
		title.set(18, "被替代标准");
		title.set(19, "标准状态");
		title.set(20, "作废日期");
		title.set(21, "过渡期");
		title.set(22, "文本名称");
		title.set(23, "语种");
		title.set(24, "标准页数");
		title.set(25, "版本");
		title.set(26, "归口单位");
		title.set(27, "摘要");
		title.set(28, "分类等级1");
		title.set(29, "分类等级2");
		title.set(30, "分类等级3");
		title.set(31, "标准性质");
		title.set(32, "备注");
	}

	/**
	 * 此函数用来判断给定文件中的字符串是否是国内标准号
	 * 
	 * @param fromTxT
	 *            待读取标准号的文件
	 * @param toTxT
	 *            存放发现问题的标准号的文件
	 * @param classCode
	 *            标准类别
	 * 
	 * @return 是否完全通过验证
	 */
	public static void isGNStandardCodeFromTXTFile(File fromTxT, File toTxT,
			ArrayList<String> classCode) {

		String encoding = "GBK";

		// 读出的标准信息
		List<String> readLines = null;

		// 存放不正确的标准号信息
		ArrayList<String> wrongStdCode = new ArrayList<String>();

		try {
			readLines = FileUtils.readLines(fromTxT, encoding);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		// 开始逐条查询
		for (String st_code : readLines) {
			// count ++;
			st_code = st_code.trim();

			log.info(st_code);

			if (StandardUtils.isGNStandardCode(st_code, classCode))
				continue;
			else {
				wrongStdCode.add(st_code);
			}
		}

		if (!wrongStdCode.isEmpty()) {

			try {
				FileUtils.writeLines(toTxT, wrongStdCode);
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	/**
	 * 
	 * 根据给定的标准号，从平台中查找题录信息，因为可以设定不同的查询条件，所以每次都要根据情况重新改写这个文件
	 * 
	 * @param localDir
	 *            放置要生成的excel文件的文件夹
	 * @param fromTXT
	 *            要读取的包含标准号的 txt 文件，查询关键字一行
	 */

	@SuppressWarnings("unchecked")
	public void getTiLuFromTXTFile(File localDir, File fromTXT) {

		String encoding = "GBK";

		Connection oracle_conn = null;
		Statement oracle_stmt = null;
		ResultSet oracle_rs = null;
		Oracle_std oracle = new Oracle_std();

		ExcelUtils eu = new ExcelUtils();

		// 存放所有的行
		ArrayList<ArrayList<String>> allLineContent = new ArrayList<ArrayList<String>>();

		allLineContent.add(singleLineContent);

		// 存放查不到的题录信息
		ArrayList<String> haveNoStdcode = new ArrayList<String>();

		List<String> readLines = null;

		try {
			readLines = FileUtils.readLines(fromTXT, encoding);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		int haveNo = 0;
		int haveYes = 0;

		String STANDARD_CODE = "";
		String NAMECN = "";
		String NAMEEN = "";
		String issue_time = "";
		String ACT_TIME = "";
		String ISSUE_DEPT = "";
		String REQUEST_DEPT = "";
		String DRAFT_DEPT = "";
		String DRAFTER = "";
		String CCS_CODE = "";
		String ICS_CODE = "";
		String ADOPT_GRADE = "";
		String ADOPT_STANDARD_CODE = "";
		String ADOPT_STANDARD_NAME = "";
		String TOPIC = "";
		String SCOPE = "";
		String REFERENCE_FILE = "";
		String REPLACE_STANDARD = "";
		String REPLACED_STANDARD = "";
		String STATUS = "";
		String CANCEL_TIME = "";
		String INTERIM = "";
		String TEXT_ADDRESS = "";
		String LANGUAGE_CODE = "";
		String PAGE_NUMBER = "";
		String EDITION = "";
		String ATTACH_DEPT = "";
		String SUMMARY = "";
		String CLASS_ONE_CODE = "";
		String CLASS_TWO_CODE = "";
		String CLASS_THREE_CODE = "";
		String CLASS_FOUR_CODE = "";
		String REMARK = "";

		// 检验环境
		if (!localDir.exists())
			localDir.mkdir();

		// 连接数据库
		try {
			oracle_conn = oracle.getConnection(oracle_conn);
			// 为了支持 oracle_rs.beforeFirst() 方法，createStatement 带了参数
			oracle_stmt = oracle_conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}

		// 开始逐条查询
		for (String stdcode : readLines) {
			stdcode = stdcode.trim();

			// 可以组建不同的条件
			// 标准号 模糊查询： %DL% 4__-%
			String condition = "t.standard_code like '" + stdcode
					+ "' and t.text_address is not null";

			// 标准号 精确查询：
			// String condition ="t.standard_code = '"+ keyWord+ "' and
			// t.text_address is not null";

			// 起草单位模糊查询 t.draft_dept like '%紫光%'
			// String condition ="t.draft_dept like '"+ keyWord+ "' and
			// t.text_address is not null";

			String query = "select STANDARD_CODE,NAMECN,NAMEEN,TO_CHAR(t.issue_time,'yyyy-mm-dd') as issue_time,TO_CHAR(t.ACT_TIME,'yyyy-mm-dd') as ACT_TIME,"
					+ "ISSUE_DEPT,REQUEST_DEPT,DRAFT_DEPT,DRAFTER,CCS_CODE,ICS_CODE,ADOPT_GRADE,ADOPT_STANDARD_CODE,ADOPT_STANDARD_NAME,TOPIC,SCOPE,REFERENCE_FILE,"
					+ "REPLACE_STANDARD,REPLACED_STANDARD,STATUS,TO_CHAR(t.CANCEL_TIME,'yyyy-mm-dd') as CANCEL_TIME,INTERIM,TEXT_ADDRESS,LANGUAGE_CODE,PAGE_NUMBER,"
					+ "EDITION,ATTACH_DEPT,SUMMARY,CLASS_ONE_CODE,CLASS_TWO_CODE,CLASS_THREE_CODE,CLASS_FOUR_CODE,REMARK "
					+ "from st_standard t where " + condition;

			log.info("query: " + query);

			try {
				oracle_rs = oracle_stmt.executeQuery(query);
				if (!oracle_rs.next()) {
					// 库中无题录的
					log.info("have no");
					haveNoStdcode.add(stdcode);
					haveNo++;
					continue;
				}

				// 因为经过了 !oracle_rs.next() 语句,指针就不子第一位了,故用下面的方法
				oracle_rs.beforeFirst();
				while (oracle_rs.next()) {
					haveYes++;
					// 库中有题录的
					STANDARD_CODE = oracle_rs.getString("STANDARD_CODE");
					NAMECN = oracle_rs.getString("NAMECN");
					NAMEEN = oracle_rs.getString("NAMEEN");
					issue_time = oracle_rs.getString("issue_time");
					ACT_TIME = oracle_rs.getString("ACT_TIME");

					ISSUE_DEPT = oracle_rs.getString("ISSUE_DEPT");
					REQUEST_DEPT = oracle_rs.getString("REQUEST_DEPT");
					DRAFT_DEPT = oracle_rs.getString("DRAFT_DEPT");
					DRAFTER = oracle_rs.getString("DRAFTER");
					CCS_CODE = oracle_rs.getString("CCS_CODE");
					ICS_CODE = oracle_rs.getString("ICS_CODE");
					ADOPT_GRADE = oracle_rs.getString("ADOPT_GRADE");
					ADOPT_STANDARD_CODE = oracle_rs
							.getString("ADOPT_STANDARD_CODE");
					ADOPT_STANDARD_NAME = oracle_rs
							.getString("ADOPT_STANDARD_NAME");
					TOPIC = oracle_rs.getString("TOPIC");
					SCOPE = oracle_rs.getString("SCOPE");
					REFERENCE_FILE = oracle_rs.getString("REFERENCE_FILE");
					REPLACE_STANDARD = oracle_rs.getString("REPLACE_STANDARD");
					REPLACED_STANDARD = oracle_rs
							.getString("REPLACED_STANDARD");
					STATUS = oracle_rs.getString("STATUS");
					CANCEL_TIME = oracle_rs.getString("CANCEL_TIME");
					INTERIM = oracle_rs.getString("INTERIM");

					TEXT_ADDRESS = oracle_rs.getString("TEXT_ADDRESS");
					TEXT_ADDRESS = StringUtils.substringAfterLast(TEXT_ADDRESS,
							"/");

					LANGUAGE_CODE = oracle_rs.getString("LANGUAGE_CODE");
					PAGE_NUMBER = oracle_rs.getString("PAGE_NUMBER");
					EDITION = oracle_rs.getString("EDITION");
					ATTACH_DEPT = oracle_rs.getString("ATTACH_DEPT");
					SUMMARY = oracle_rs.getString("SUMMARY");
					CLASS_ONE_CODE = oracle_rs.getString("CLASS_ONE_CODE");
					CLASS_TWO_CODE = oracle_rs.getString("CLASS_TWO_CODE");
					CLASS_THREE_CODE = oracle_rs.getString("CLASS_THREE_CODE");
					CLASS_FOUR_CODE = oracle_rs.getString("CLASS_FOUR_CODE");
					REMARK = oracle_rs.getString("REMARK");

					singleLineContent = new ArrayList<String>();// 重新初始化
					singleLineContent.add(STANDARD_CODE);
					singleLineContent.add(NAMECN);
					singleLineContent.add(NAMEEN);
					singleLineContent.add(issue_time);
					singleLineContent.add(ACT_TIME);
					singleLineContent.add(ISSUE_DEPT);
					singleLineContent.add(REQUEST_DEPT);
					singleLineContent.add(DRAFT_DEPT);
					singleLineContent.add(DRAFTER);
					singleLineContent.add(CCS_CODE);
					singleLineContent.add(ICS_CODE);

					singleLineContent.add(ADOPT_GRADE);
					singleLineContent.add(ADOPT_STANDARD_CODE);
					singleLineContent.add(ADOPT_STANDARD_NAME);
					singleLineContent.add(TOPIC);
					singleLineContent.add(SCOPE);
					singleLineContent.add(REFERENCE_FILE);
					singleLineContent.add(REPLACE_STANDARD);
					singleLineContent.add(REPLACED_STANDARD);
					singleLineContent.add(STATUS);
					singleLineContent.add(CANCEL_TIME);
					singleLineContent.add(INTERIM);
					singleLineContent.add(TEXT_ADDRESS);

					singleLineContent.add(LANGUAGE_CODE);
					singleLineContent.add(PAGE_NUMBER);
					singleLineContent.add(EDITION);
					singleLineContent.add(ATTACH_DEPT);
					singleLineContent.add(SUMMARY);
					singleLineContent.add(CLASS_ONE_CODE);
					singleLineContent.add(CLASS_TWO_CODE);
					singleLineContent.add(CLASS_THREE_CODE);
					singleLineContent.add(CLASS_FOUR_CODE);
					singleLineContent.add(REMARK);

					allLineContent.add(singleLineContent);
				}

			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				oracle.closeDB(oracle_rs, oracle_stmt, oracle_conn);
				e1.printStackTrace();
			}
		}// end for

		oracle.closeDB(oracle_rs, oracle_stmt, oracle_conn);

		// 开始写
		eu.writeArrayListArrayListValueToExcel(new File(localDir.getPath()
				+ "/havaStdcode_" + MyDateUtils.getToday("yyyy-MM-dd-HH.mm.ss")
				+ ".xls"), allLineContent, true);

		if (haveNo != 0)
			eu.writeArrayListStringValueToExcel(new File(localDir.getPath()
					+ "/haveNoStdcode_"
					+ MyDateUtils.getToday("yyyy-MM-dd-HH.mm.ss") + ".xls"),
					haveNoStdcode);

		log.info("have tilu :" + haveYes);
		log.info("have not tilu :" + haveNo);
	}

	/**
	 * 本函数用来检查并规范标准号
	 * 
	 * @param fromTxT
	 *            待查询的txt文件,每行一个标准号
	 * @param toExcel
	 *            规范后的 excel,第一列为序号,第二列为规范前的标准号,第三列为规范后的标准号
	 */
	@SuppressWarnings("unchecked")
	public void getRightStandardCode(File fromTxT, File toExcel) {

		ArrayList<ArrayList<String>> al = new ArrayList<ArrayList<String>>();
		ArrayList<String> classCode = StandardClassUtils.getClassCode_GN();
		ExcelUtils eu = new ExcelUtils();

		String codeNew = "";
		try {
			List<String> stdCodeList = FileUtils.readLines(fromTxT, "GBK");

			for (String code : stdCodeList) {
				codeNew = StandardUtils.getStandardCode(code, classCode);

				ArrayList<String> tempList = new ArrayList<String>();

				tempList.add(code);

				if (codeNew != null)
					tempList.add(codeNew);

				al.add(tempList);

			}

			eu.writeArrayListArrayListValueToExcel(toExcel, al, true);

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 拆分引用文件
	 * 
	 * @param fromTXT
	 *            待查询的 TXT 文件,每个标准号一行
	 * @param toTXT
	 *            拆分后,生成的引用文件标准号,并且去掉重复
	 */
	public void getReferenceFilesNameByToken(File fromTXT, File toTXT) {

		HashSet<String> resultSet = new HashSet<String>();
		int c = 0;
		try {
			List<String> stdCodeList = FileUtils.readLines(fromTXT, "GBK");
			for (String code : stdCodeList) {
				String[] st = Utils.getStringbyToken(code, ";");
				for (String s : st) {
					c++;
					// s=StringUtils.substringBefore(s, "(");
					resultSet.add(s.trim());
				}
				log.info("element :" + code);
			}

			FileUtils.writeLines(toTXT, "GBK", resultSet, null);

			log.info("al befor :" + c);
			log.info("al after :" + resultSet.size());

		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		TiLuUtils tu = new TiLuUtils();
		// tu.getTiLuFromExcel("D:\\TiLuFolder\\18.xls", 1, 1);
		// tu.getReferenceFilesName3(new File("D:\\Edit4.TXT"), null);
		// tu.getReferenceFilesName2(new File("D:\\get_s.txt"), new
		// File("D:\\get.txt"));

		tu.getTiLuFromTXTFile(new File("D:\\TiLuFolder"), new File(
				"D:\\TiLuFolder\\l.txt"));

	}
}
